%Replication code for Table 2

% Table 2
% Summary statistics for credit card asset-backed securities
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

% This table presents summary statistics for the individual A, B, and C tranches 
% of the credit card securitizations in the sample. Mat denotes the average maturity 
% of the tranches in years. Sprd denotes the average floating spread above Libor 
% of the tranches and is expressed in basis points (fixed coupon rates are swapped
% into floating coupon rates for the purposes of this table). Attach and Detach 
% denote the average attachment and detachment points for individual tranches 
% and are expressed as percentages of the total notional amount of the securitization. 
% Min, Mean, and Max denote the minimum, mean, and maximum prices for the individual 
% tranches during the sample period. The statistics for the individual card issuers 
% are based on the daily values across all of their outstanding securitizations. 
% The sample is daily from January 1, 2000 to January 31, 2020.


clear; clc;

%Functions for summary stats
oMean = @(x) mean(x,1,'omitnan');
oMedian = @(x) median(x,1,'omitnan');
oStd = @(x) std(x,0,'omitnan');
oMin = @(x) min(x,[],'omitnan');
oMax = @(x) max(x,[],'omitnan');
oLQrt = @(x) prctile(x,25,1); 
oUQrt = @(x) prctile(x,75,1); 
oNObs = @(x) sum(~isnan(x),1); 


%A/B/C Tranche Triplets
opts = spreadsheetImportOptions("NumVariables", 18);
opts.Sheet = "AllCreditCards_Triplets";
opts.DataRange = "A2:R26981";
opts.VariableNames = ["MasterTrustNo", "NoMonthsExpMat_A", "NoMonthsExpMat_B", "NoMonthsExpMat_C", ...
    "Att_A", "Att_B", "Att_C", "Det_A", "Det_B", "Det_C", "Px_A", "Px_B", "Px_C", ...
    "Year", "FltSpreadL3_A", "FltSpreadL3_B", "FltSpreadL3_C", "SellersInt"];
opts.VariableTypes = ["double", "double", "double", "double", "double", "double", "double", ...
    "double", "double", "double", "double", "double", "double", "double", ...
    "double", "double", "double", "double"];
CreditCardAbsDataset = readtable(".\Data_Table_02.xlsx", opts, "UseExcel", false);
clear opts
CreditCardAbsDataset = standardizeMissing(CreditCardAbsDataset,-999);
CreditCardAbsDataset = CreditCardAbsDataset(CreditCardAbsDataset.Year>=2000,:);%sample period starting in 2000




%create issuer index
% 	1. Amex; 2. Bank of America; 3. Bank One; 4. Capital One; 5. Chase;
%   6. Citi; 7. Discover; 8. First National; 9. MBNA; 10. World Financial Network
issuerNames = {'Amex','BoA','BnkOne','Citi','Chase','CapOne','Disc','FrstNat','MBNA','WrldFin','Other'};
IssuerNo = NaN(size(CreditCardAbsDataset,1),1);
IssuerName = strings(size(CreditCardAbsDataset,1),1);
for idx=1:length(IssuerNo)
   currMasterTrustNo = CreditCardAbsDataset.MasterTrustNo(idx);
   if (currMasterTrustNo ==3)
       IssuerName(idx) = "Amex";
       IssuerNo(idx) = 1;
   elseif (currMasterTrustNo==4 || currMasterTrustNo ==5)
       IssuerName(idx) = "BoA";
       IssuerNo(idx) = 2;
   elseif (currMasterTrustNo==6 || currMasterTrustNo ==7)
       IssuerName(idx) = "BnkOne";
       IssuerNo(idx) = 3;
   elseif (currMasterTrustNo==8)
       IssuerName(idx) = "Citi";
       IssuerNo(idx) = 4;
   elseif (currMasterTrustNo==9 || currMasterTrustNo ==10)
       IssuerName(idx) = "Chase";
       IssuerNo(idx) = 5;
   elseif (currMasterTrustNo==11 || currMasterTrustNo ==12)
       IssuerName(idx) = "CapOne";
       IssuerNo(idx) = 6;
   elseif (currMasterTrustNo==13 || currMasterTrustNo ==14)
       IssuerName(idx) = "Disc";
       IssuerNo(idx) = 7;
   elseif (currMasterTrustNo==15)
       IssuerName(idx) = "FrstNat";
       IssuerNo(idx) = 8;
   elseif (currMasterTrustNo==16 || currMasterTrustNo ==17)
       IssuerName(idx) = "MBNA";
       IssuerNo(idx) = 9;
   elseif (currMasterTrustNo==19)
       IssuerName(idx) = "WrldFin";
       IssuerNo(idx) = 10;
   else 
       IssuerName(idx) = "Other";
       IssuerNo(idx) = 0;
   end
end
CreditCardAbsDataset.IssuerName = IssuerName;
CreditCardAbsDataset.IssuerNo = IssuerNo;
CreditCardAbsDataset = CreditCardAbsDataset(IssuerNo~=0,:);







%% A Tranche 
%Aggregate by trust and A tranche

CreditCardAbsDataset_A = CreditCardAbsDataset(:,["IssuerName", "NoMonthsExpMat_A",...
    "FltSpreadL3_A","Att_A", "Det_A", "Px_A"]);

%Filter:
%missing data on A tranche
idxDrop = isnan(CreditCardAbsDataset_A.NoMonthsExpMat_A) | ismissing(CreditCardAbsDataset_A.NoMonthsExpMat_A) | ...
          isnan(CreditCardAbsDataset_A.Att_A) | ismissing(CreditCardAbsDataset_A.Att_A) | ...
          isnan(CreditCardAbsDataset_A.Det_A) | ismissing(CreditCardAbsDataset_A.Det_A) | ...
          isnan(CreditCardAbsDataset_A.Px_A) | ismissing(CreditCardAbsDataset_A.Px_A);
CreditCardAbsDataset_A = CreditCardAbsDataset_A(~idxDrop,:);
%ensure >=12 months to maturity A tranche
idxDrop = CreditCardAbsDataset_A.NoMonthsExpMat_A < 12.0;
CreditCardAbsDataset_A = CreditCardAbsDataset_A(~idxDrop,:);
%ensure <=240 months to maturity A tranche
idxDrop = CreditCardAbsDataset_A.NoMonthsExpMat_A > 240.0;
CreditCardAbsDataset_A = CreditCardAbsDataset_A(~idxDrop,:);



%Attachment/Detachment point to percent
CreditCardAbsDataset_A{:,{'Att_A','Det_A'}} = CreditCardAbsDataset_A{:,{'Att_A','Det_A'}}.*100;
%Months to maturity to years to maturity
CreditCardAbsDataset_A{:,{'NoMonthsExpMat_A'}} = CreditCardAbsDataset_A{:,{'NoMonthsExpMat_A'}}./12;



% Compute Averages by issuer
CreditCardAbsDataset_A_Issuer = grpstats(CreditCardAbsDataset_A,{'IssuerName'},...
    'mean','DataVars',{'NoMonthsExpMat_A','FltSpreadL3_A','Att_A','Det_A','Px_A'},...
    'VarNames',{'Issuer','N','Mat','Sprd','Attach','Detach','Px_Mean'});
%Min Px by Issuer
tmpMinPx = grpstats(CreditCardAbsDataset_A,{'IssuerName'},...
    'min','DataVars',{'Px_A'},...
    'VarNames',{'Issuer','N','Px_Min'});
%Max Px by Issuer
tmpMaxPx = grpstats(CreditCardAbsDataset_A,{'IssuerName'},...
    'max','DataVars',{'Px_A'},...
    'VarNames',{'Issuer','N','Px_Max'});

CreditCardAbsDataset_A_Issuer.Px_Min = tmpMinPx.Px_Min;
CreditCardAbsDataset_A_Issuer.Px_Max = tmpMaxPx.Px_Max;
CreditCardAbsDataset_A_Issuer.Tranche = repmat("A",10,1);
CreditCardAbsDataset_A_Issuer = CreditCardAbsDataset_A_Issuer(:,...
    {'Tranche','Mat','Sprd','Attach','Detach','Px_Min','Px_Mean','Px_Max','N'});
CreditCardAbsDataset_A_Issuer.Properties.RowNames = strcat(CreditCardAbsDataset_A_Issuer.Properties.RowNames,"_A");
CreditCardAbsDataset_A = CreditCardAbsDataset_A_Issuer;


%% Tranche B
%Aggregate by trust and B Tranche


CreditCardAbsDataset_B = CreditCardAbsDataset(:,["IssuerName", "NoMonthsExpMat_B",...
    "FltSpreadL3_B","Att_B", "Det_B", "Px_B"]);

%Filter:
%missing data on B tranche
idxDrop = isnan(CreditCardAbsDataset_B.NoMonthsExpMat_B) | ismissing(CreditCardAbsDataset_B.NoMonthsExpMat_B) | ...
          isnan(CreditCardAbsDataset_B.Att_B) | ismissing(CreditCardAbsDataset_B.Att_B) | ...
          isnan(CreditCardAbsDataset_B.Det_B) | ismissing(CreditCardAbsDataset_B.Det_B) | ...
          isnan(CreditCardAbsDataset_B.Px_B) | ismissing(CreditCardAbsDataset_B.Px_B);
CreditCardAbsDataset_B = CreditCardAbsDataset_B(~idxDrop,:);
%ensure tranche B detaches at <1.00
idxDrop = CreditCardAbsDataset_B.Det_B >= 1.00;
CreditCardAbsDataset_B = CreditCardAbsDataset_B(~idxDrop,:);
%ensure >=12 months to maturity C tranche
idxDrop = CreditCardAbsDataset_B.NoMonthsExpMat_B < 12.0;
CreditCardAbsDataset_B = CreditCardAbsDataset_B(~idxDrop,:);
%ensure <=240 months to maturity C tranche
idxDrop = CreditCardAbsDataset_B.NoMonthsExpMat_B > 240.0;
CreditCardAbsDataset_B = CreditCardAbsDataset_B(~idxDrop,:);



%Attachment/Detachment point to percent
CreditCardAbsDataset_B{:,{'Att_B','Det_B'}} = CreditCardAbsDataset_B{:,{'Att_B','Det_B'}}.*100;
%Months to maturity to years to maturity
CreditCardAbsDataset_B{:,{'NoMonthsExpMat_B'}} = CreditCardAbsDataset_B{:,{'NoMonthsExpMat_B'}}./12;



% Compute Averages by issuer
CreditCardAbsDataset_B_Issuer = grpstats(CreditCardAbsDataset_B,{'IssuerName'},...
    'mean','DataVars',{'NoMonthsExpMat_B','FltSpreadL3_B','Att_B','Det_B','Px_B'},...
    'VarNames',{'Issuer','N','Mat','Sprd','Attach','Detach','Px_Mean'});
%Min Px by Issuer
tmpMinPx = grpstats(CreditCardAbsDataset_B,{'IssuerName'},...
    'min','DataVars',{'Px_B'},...
    'VarNames',{'Issuer','N','Px_Min'});
%Max Px by Issuer
tmpMaxPx = grpstats(CreditCardAbsDataset_B,{'IssuerName'},...
    'max','DataVars',{'Px_B'},...
    'VarNames',{'Issuer','N','Px_Max'});

CreditCardAbsDataset_B_Issuer.Px_Min = tmpMinPx.Px_Min;
CreditCardAbsDataset_B_Issuer.Px_Max = tmpMaxPx.Px_Max;
CreditCardAbsDataset_B_Issuer.Tranche = repmat("B",10,1);
CreditCardAbsDataset_B_Issuer = CreditCardAbsDataset_B_Issuer(:,...
    {'Tranche','Mat','Sprd','Attach','Detach','Px_Min','Px_Mean','Px_Max','N'});
CreditCardAbsDataset_B_Issuer.Properties.RowNames = strcat(CreditCardAbsDataset_B_Issuer.Properties.RowNames,"_B");

CreditCardAbsDataset_B = CreditCardAbsDataset_B_Issuer;




%% Tranche C
%Aggregate by trust and C tranche

CreditCardAbsDataset_C = CreditCardAbsDataset(:,["IssuerName", "NoMonthsExpMat_C",...
    "FltSpreadL3_C","Att_C", "Det_C", "Px_C"]);

%Filter:
%missing data on C tranche
idxDrop = isnan(CreditCardAbsDataset_C.NoMonthsExpMat_C) | ismissing(CreditCardAbsDataset_C.NoMonthsExpMat_C) | ...
          isnan(CreditCardAbsDataset_C.Att_C) | ismissing(CreditCardAbsDataset_C.Att_C) | ...
          isnan(CreditCardAbsDataset_C.Det_C) | ismissing(CreditCardAbsDataset_C.Det_C) | ...
          isnan(CreditCardAbsDataset_C.Px_C) | ismissing(CreditCardAbsDataset_C.Px_C);
CreditCardAbsDataset_C = CreditCardAbsDataset_C(~idxDrop,:);
%prices < 10 on C tranche
idxDrop = CreditCardAbsDataset_C.Px_C < 10.0;
CreditCardAbsDataset_C = CreditCardAbsDataset_C(~idxDrop,:);
%special logic for world financial (c tranche)
idx = (CreditCardAbsDataset_C.IssuerName=="WrldFin") & (CreditCardAbsDataset_C.Att_C<=0.41);
CreditCardAbsDataset_C.Att_C(idx) = 0.0;
%ensure tranche C attaches at zero
idxDrop = CreditCardAbsDataset_C.Att_C > 0.0;
CreditCardAbsDataset_C = CreditCardAbsDataset_C(~idxDrop,:);
%ensure >=12 months to maturity C tranche
idxDrop = CreditCardAbsDataset_C.NoMonthsExpMat_C < 12.0;
CreditCardAbsDataset_C = CreditCardAbsDataset_C(~idxDrop,:);
%ensure <=240 months to maturity C tranche
idxDrop = CreditCardAbsDataset_C.NoMonthsExpMat_C > 240.0;
CreditCardAbsDataset_C = CreditCardAbsDataset_C(~idxDrop,:);



%Attachment/Detachment point to percent
CreditCardAbsDataset_C{:,{'Att_C','Det_C'}} = CreditCardAbsDataset_C{:,{'Att_C','Det_C'}}.*100;
%Months to maturity to years to maturity
CreditCardAbsDataset_C{:,{'NoMonthsExpMat_C'}} = CreditCardAbsDataset_C{:,{'NoMonthsExpMat_C'}}./12;



% Compute Averages by issuer
CreditCardAbsDataset_C_Issuer = grpstats(CreditCardAbsDataset_C,{'IssuerName'},...
    'mean','DataVars',{'NoMonthsExpMat_C','FltSpreadL3_C','Att_C','Det_C','Px_C'},...
    'VarNames',{'Issuer','N','Mat','Sprd','Attach','Detach','Px_Mean'});
%Min Px by Issuer
tmpMinPx = grpstats(CreditCardAbsDataset_C,{'IssuerName'},...
    'min','DataVars',{'Px_C'},...
    'VarNames',{'Issuer','N','Px_Min'});
%Max Px by Issuer
tmpMaxPx = grpstats(CreditCardAbsDataset_C,{'IssuerName'},...
    'max','DataVars',{'Px_C'},...
    'VarNames',{'Issuer','N','Px_Max'});

CreditCardAbsDataset_C_Issuer.Px_Min = tmpMinPx.Px_Min;
CreditCardAbsDataset_C_Issuer.Px_Max = tmpMaxPx.Px_Max;
CreditCardAbsDataset_C_Issuer.Tranche = repmat("C",9,1);
CreditCardAbsDataset_C_Issuer = CreditCardAbsDataset_C_Issuer(:,...
    {'Tranche','Mat','Sprd','Attach','Detach','Px_Min','Px_Mean','Px_Max','N'});
CreditCardAbsDataset_C_Issuer.Properties.RowNames = strcat(CreditCardAbsDataset_C_Issuer.Properties.RowNames,"_C");

CreditCardAbsDataset_C = CreditCardAbsDataset_C_Issuer;





%% Stack to build Table 2
Tab_2 = [CreditCardAbsDataset_A; CreditCardAbsDataset_B; CreditCardAbsDataset_C];
Tab_2.Sprd = [];
Tab_2.Tranche = [];


%to cell
%format significant digits
tmpFun = @(x) sprintf('%3.2f',x);
tmpTbl_Cell = cellfun(tmpFun,num2cell(table2array(Tab_2)),...
    'UniformOutput',false);
tmpTbl_Cell = [ [{''};Tab_2.Properties.RowNames] ...
    [Tab_2.Properties.VariableNames; tmpTbl_Cell]];


%Output to command window
printtable(tmpTbl_Cell(2:end,2:end),'colVarNames', tmpTbl_Cell(1,2:end),...
    'rowVarNames', tmpTbl_Cell(2:end,1));


%RESULTS
%NOTE: Spread column calculated manually in Excel for all L3 securitizations
% +-----------+------+----------+--------+--------+--------+---------+--------+---------+
% |           | Mat  |  Spread  | Attach | Detach | Px_Min | Px_Mean | Px_Max |    N    |
% +-----------+------+----------+--------+--------+--------+---------+--------+---------+
% |  Amex_A   | 3.21 |          | 15.04  | 100.00 | 63.97  |  99.71  | 110.98 | 3790.00 |
% |   BoA_A   | 2.75 |          | 18.09  | 100.00 | 73.43  |  98.53  | 100.86 | 577.00  |
% | BnkOne_A  | 2.85 |          | 15.82  | 100.00 | 95.75  | 100.05  | 102.99 | 427.00  |
% |  Citi_A   | 3.46 |          | 12.98  | 100.00 | 79.52  |  99.44  | 112.06 | 1229.00 |
% |  Chase_A  | 3.07 |          | 16.27  | 100.00 | 61.57  |  99.83  | 110.29 | 1815.00 |
% | CapOne_A  | 3.52 |          | 20.02  | 100.00 | 55.39  | 101.20  | 114.91 | 1865.00 |
% |  Disc_A   | 3.09 |          | 12.50  | 100.00 | 68.06  |  99.98  | 111.58 | 2596.00 |
% | FrstNat_A | 2.00 |          | 20.23  | 100.00 | 92.51  |  99.92  | 101.12 | 195.00  |
% |  MBNA_A   | 3.66 |          | 15.28  | 100.00 | 78.47  | 100.79  | 123.67 | 4199.00 |
% | WrldFin_A | 2.94 |          | 24.69  | 100.00 | 59.05  | 100.55  | 108.64 | 1387.00 |
% +-----------+------+----------+--------+--------+--------+---------+--------+---------+
% |  Amex_B   | 3.22 |          |  8.46  | 15.07  | 34.65  |  98.42  | 119.86 | 3722.00 |
% |   BoA_B   | 2.76 |          |  9.54  | 18.09  | 40.32  |  96.16  | 101.91 | 584.00  |
% | BnkOne_B  | 2.84 |          |  8.75  | 15.84  | 96.43  |  99.98  | 101.91 | 424.00  |
% |  Citi_B   | 3.43 |          |  7.78  | 12.98  | 52.66  |  97.29  | 111.14 | 1224.00 |
% |  Chase_B  | 3.10 |          |  9.05  | 16.28  | 24.64  |  98.53  | 108.82 | 1851.00 |
% | CapOne_B  | 3.58 |          | 11.05  | 20.09  | 22.63  |  97.72  | 112.16 | 1803.00 |
% |  Disc_B   | 3.11 |          |  7.50  | 12.50  | 38.32  |  98.42  | 110.76 | 2656.00 |
% | FrstNat_B | 1.93 |          | 10.69  | 18.09  | 79.58  |  98.02  | 101.63 | 111.00  |
% |  MBNA_B   | 3.67 |          |  8.00  | 15.28  | 50.12  |  99.64  | 123.09 | 4216.00 |
% | WrldFin_B | 2.98 |          | 16.51  | 23.29  | 49.06  |  99.48  | 109.85 | 852.00  |
% +-----------+------+----------+--------+--------+--------+---------+--------+---------+
% |  Amex_C   | 3.39 |          |  0.00  |  8.14  | 20.45  |  93.43  | 104.15 | 1312.00 |
% |   BoA_C   | 2.87 |          |  0.00  | 10.22  | 41.07  |  93.29  | 110.06 | 395.00  |
% | BnkOne_C  | 3.95 |          |  0.00  |  8.30  | 27.81  |  97.87  | 107.32 | 508.00  |
% |  Citi_C   | 4.01 |          |  0.00  |  7.76  | 23.27  |  97.12  | 113.54 | 1282.00 |
% |  Chase_C  | 3.27 |          |  0.00  |  9.14  | 15.97  |  97.87  | 104.27 | 1558.00 |
% | CapOne_C  | 3.43 |          |  0.00  | 11.34  | 11.44  |  97.05  | 113.89 | 1590.00 |
% |  Disc_C   | 2.00 |          |  0.00  | 26.86  | 61.40  |  86.44  | 100.20 |  25.00  |
% | FrstNat_C | 1.99 |          |  0.00  |  9.50  | 67.50  |  97.87  | 102.41 |  99.00  |
% |  MBNA_C   | 4.00 |          |  0.00  |  8.13  | 27.22  | 100.38  | 118.83 | 3051.00 |
% +-----------+------+----------+--------+--------+--------+---------+--------+---------+




